Create PROC [dbo].[CopySupplementaryInsuranceDrug]
    @OriginalDrugCode VARCHAR(15),
    @DestinationDrugCode VARCHAR(15)
AS
BEGIN
    ---------------Copy Basic Drug Information
    SET NOCOUNT ON;

    DECLARE @StatusCode SMALLINT = 0;
    DECLARE @StatusMsg NVARCHAR(100);

    BEGIN TRY
        BEGIN TRAN;

        SET @StatusCode = 100;
        SET @StatusMsg = N'       ';

        IF @OriginalDrugCode = @DestinationDrugCode
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;


        SET @StatusCode = 101;
        SET @StatusMsg = N'     ';

        IF NOT EXISTS (SELECT 1 FROM dbo.KalaId WHERE K_Code = @OriginalDrugCode)
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 102;
        SET @StatusMsg = N'     ';

        IF NOT EXISTS
        (
            SELECT 1
            FROM dbo.KalaId
            WHERE K_Code = @DestinationDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);


        SET @StatusCode = 103;
        SET @StatusMsg = N'       ';


        IF NOT EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.Anbar
            WHERE K_Code = @OriginalDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 104;
        SET @StatusMsg = N'       ';

        IF NOT EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.Anbar
            WHERE K_Code = @DestinationDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);


        ----------------------------------------------------------- Supplementary Insurancce 
        DELETE FROM dbo.SupplementaryInsurance_DrugCondition
        WHERE GoodsCode = @DestinationDrugCode;


        IF EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.SupplementaryInsurance_DrugCondition
            WHERE GoodsCode = @OriginalDrugCode
        )
        BEGIN
            SET @StatusCode = 110;
            SET @StatusMsg = N'       ʘ';

            INSERT INTO dbo.SupplementaryInsurance_DrugCondition
            (
                GoodsCode,
                InsuranceCode,
                NonInsuranceAmount,
                PatientAmount,
                DifferAmount,
                GoodsAmount,
                MessageId,
                Ceilling,
                IsSpecialDrug
            )
            SELECT @DestinationDrugCode,
                   InsuranceCode,
                   NonInsuranceAmount,
                   PatientAmount,
                   DifferAmount,
                   GoodsAmount,
                   MessageId,
                   Ceilling,
                   IsSpecialDrug
            FROM dbo.SupplementaryInsurance_DrugCondition
            WHERE GoodsCode = @OriginalDrugCode;

        END;

        ----------------------------------------------------------- End of Operaion

        SET @StatusCode = 1;
        SET @StatusMsg = N'     ';

        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;
        PRINT ERROR_MESSAGE();
        ROLLBACK TRAN;
    END CATCH;
END;